/*
 Navicat Premium Data Transfer

 Source Server         : SQLServer
 Source Server Type    : SQL Server
 Source Server Version : 16001000 (16.00.1000)
 Source Host           : localhost:1433
 Source Catalog        : Teach
 Source Schema         : dbo

 Target Server Type    : SQL Server
 Target Server Version : 16001000 (16.00.1000)
 File Encoding         : 65001

 Date: 29/09/2025 09:40:30
*/


-- ----------------------------
-- Table structure for T
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[T]') AND type IN ('U'))
	DROP TABLE [dbo].[T]
GO

CREATE TABLE [dbo].[T] (
  [TNO] nchar(10) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [TN] nvarchar(50) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [Sex] nchar(2) COLLATE Chinese_PRC_CI_AS DEFAULT '男' NOT NULL,
  [Age] int  NOT NULL,
  [Prof] nchar(10) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [Sal] numeric(18,2)  NOT NULL,
  [Comm] numeric(18,2)  NOT NULL,
  [Dept] nchar(20) COLLATE Chinese_PRC_CI_AS  NOT NULL
)
GO

ALTER TABLE [dbo].[T] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of T
-- ----------------------------
INSERT INTO [dbo].[T] ([TNO], [TN], [Sex], [Age], [Prof], [Sal], [Comm], [Dept]) VALUES (N'T100      ', N'黎明', N'女 ', N'80', N'教授        ', N'500.00', N'800.00', N'计算机系                ')
GO

INSERT INTO [dbo].[T] ([TNO], [TN], [Sex], [Age], [Prof], [Sal], [Comm], [Dept]) VALUES (N'T11       ', N'李力', N'男 ', N'47', N'教授        ', N'2300.00', N'3000.00', N'计算机                 ')
GO

INSERT INTO [dbo].[T] ([TNO], [TN], [Sex], [Age], [Prof], [Sal], [Comm], [Dept]) VALUES (N'T2        ', N'王平', N'女 ', N'28', N'副教授       ', N'1100.00', N'1200.00', N'信息                  ')
GO

INSERT INTO [dbo].[T] ([TNO], [TN], [Sex], [Age], [Prof], [Sal], [Comm], [Dept]) VALUES (N'T3        ', N'刘伟', N'男 ', N'30', N'教授        ', N'1400.00', N'1200.00', N'计算机                 ')
GO

INSERT INTO [dbo].[T] ([TNO], [TN], [Sex], [Age], [Prof], [Sal], [Comm], [Dept]) VALUES (N'T4        ', N'张雪', N'女 ', N'51', N'教授        ', N'1600.00', N'3000.00', N'自动化                 ')
GO

INSERT INTO [dbo].[T] ([TNO], [TN], [Sex], [Age], [Prof], [Sal], [Comm], [Dept]) VALUES (N'T5        ', N'张兰', N'女 ', N'39', N'副教授       ', N'1300.00', N'2000.00', N'信息                  ')
GO


-- ----------------------------
-- Triggers structure for table T
-- ----------------------------
CREATE TRIGGER [dbo].[AutoPostCourse]
ON [dbo].[T]
WITH EXECUTE AS CALLER
FOR INSERT
AS
DECLARE @tno NCHAR(10),@name NVARCHAR(50)
SELECT @tno=TNO ,@name=TN FROM INSERTED
   INSERT INTO TC VALUES(@tno ,'C0')
PRINT @name+'老师您好！欢迎入职！系统已经自动为您排了一门课，课程号位C0,
记得去上哦！'
GO

CREATE TRIGGER [dbo].[Modify_T]
ON [dbo].[T]
WITH EXECUTE AS CALLER
FOR UPDATE
AS
update TC set TNO=(select TNO from inserted )
where TC.TNO=(select TNO from deleted)

update T set TNO='T111' where TNO='T1'
GO

CREATE TRIGGER [dbo].[SalChangeByProf]
ON [dbo].[T]
WITH EXECUTE AS CALLER
FOR UPDATE
AS
BEGIN 
declare @rs int
declare @p1 nchar(10)
declare @p2 nchar(10)
select @p1=Prof from inserted
select @p2=Prof from deleted
 

	 exec ProfCompare @p1,@p2, @rs out
      update T set Sal=Sal+500*@rs where (select Prof from inserted)='教授' AND TNO=(select  TNO from deleted)
	 -- update T set Sal=Sal+300*@rs where (select Prof from inserted)='副教授'  AND TNO=(select  TNO from deleted)
	 -- update T set Sal=Sal+100*@rs where (select Prof from inserted)='讲师' AND TNO=(select  TNO from deleted)
	END
GO


-- ----------------------------
-- Primary Key structure for table T
-- ----------------------------
ALTER TABLE [dbo].[T] ADD CONSTRAINT [PK_T] PRIMARY KEY CLUSTERED ([TNO])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO

